﻿namespace MockCommon
{
    public sealed class Constant
    {
        #region Constructor
        private Constant()
        {
        }
        #endregion

        #region Other
        public const string PatternCsvFile = "Client,Consultant,ContractType,Content,StartDate,EndDate,FirstName,LastName,Birthdate,Gender,Telephone1,Telephone2,Branch,SortCode,DayInWeek";
        #endregion

        #region BasicQuery
        public const string QuerySelect = "select {0} from {1} where {2}";
        public const string QueryInsert = "insert into {0} ({1}) values ({2})";
        public const string QueryUpdate = "update {0} set {1} where {2}";
        public const string QueryDelete = "delete {0} where {1}";
        #endregion

        #region User
        public const string UserGetAll = "select * from [User]";
        public const string UserGetUserIdByUserName = @"select [UserId] from [User] 
                                                        where [Username] = @Username";
        public const string UserGetByUserName = @"select * from [User] 
                                                  where [Username] = @Username";
        public const string UserAuthentication = @"select * from [User]
                                                   where [Username] = @Username
                                                   and [Password] = @Password";
        public const string UserExistUserName = @"select top(1) [UserId] from [User] 
                                                  where [Username] = @Username";
        public const string UserExistUserNameAndIsActive = @"select top(1) [UserId] from [User]
                                                             where [Username] = @Username
                                                             and [IsActive] = @IsActive";
        #endregion

        #region Consultant
        public const string ConsultantGetAll = "select * from [Consultant]";
        public const string ConsultantUserGetAll = @"select * from [Consultant] cs 
                                                     inner join [User] u on cs.[User] = u.[UserId]";
        public const string ConsultantGetConsultantIdByUserName = @"select [ConsultantId] from [Consultant] c
                                                                    inner join [User] u on c.[User] = u.[UserId]
                                                                    where u.[Username] = @Username";
        public const string ConsultantGetByUserName = @"select * from [Consultant] c 
                                                        inner join [User] u on c.[User] = u.[UserId] 
                                                        where u.[Username] = @Username";
        public const string ConsultantGetCountContract = @"select count([ContractId]) from [Contract] 
                                                           where [Consultant] = @ConsultantId";
        public const string ConsultantGetByFilter = @"select c.[ConsultantId], c.[User], c.[FirstName], 
                                                      c.[LastName], c.[Birthdate], c.[Gender],
                                                      c.[Telephone], c.[Address]
                                                      from [Consultant] c 
                                                      inner join [User] u on u.[UserId] = c.[User]
                                                      where u.[Username] like '%'+@Username+'%' 
                                                      and u.[Email] like '%'+@Email+'%'
                                                      and c.[FirstName] like '%'+@FirstName+'%' 
                                                      and c.[LastName] like '%'+@LastName+'%'
                                                      and u.[IsActive] like '%'+@IsActive+'%'";
        #endregion

        #region ContractType
        public const string ContractTypeGetAll = "select * from [ContractType]";
        public const string ContractTypeGetContractTypeIdByContractTypeName = @"select [ContractTypeId] from [ContractType] 
                                                                                where [ContractTypeName] = @ContractTypeName";
        #endregion

        #region Client
        public const string ClientGetAll = "select * from [Client]";
        public const string ClientUserGetAll = @"select * from [Client] cl 
                                                 inner join [User] u on cl.[User] = u.[UserId]";
        public const string ClientGetClientIdByUserName = @"select [ClientId] from [Client] c
                                                            inner join [User] u on c.[User] = u.[UserId]
                                                            where u.[Username] = @Username";
        public const string ClientGetByUserName = @"select cl.[ClientId], cl.[User], cl.[FirstName], 
	                                                cl.[LastName], cl.[Birthdate], cl.[Gender],
	                                                cl.[Title], cl.[Address], cl.[VatNumber], 
	                                                cl.[RegisterNumber], cl.[PostCode], cl.[DayInWeek] 
                                                    from [Client] cl 
                                                    inner join [User] u on u.[UserId] = cl.[User]
                                                    where u.[Username] = @Username";
        public const string ClientGetClientUserNameByContractId = @"select u.[Username] from [Client] cl 
                                                                    inner join [Contract] ct on ct.[Client] = cl.[ClientId] 
                                                                    inner join [User] u on u.[UserId] = cl.[User]
                                                                    where ct.[ContractId] = @ContractId";
        public const string ClientGetByFilter = @"select cl.[ClientId], cl.[User], cl.[FirstName], 
                                                  cl.[LastName], cl.[Birthdate], cl.[Gender],
                                                  cl.[Title], cl.[Address], cl.[VatNumber], 
                                                  cl.[RegisterNumber], cl.[PostCode], cl.[DayInWeek] 
                                                  from [Client] cl 
                                                  inner join [User] u on u.[UserId] = cl.[User]
                                                  where u.[Username] like '%'+@Username+'%' 
                                                  and u.[Email] like '%'+@Email+'%'
                                                  and cl.[FirstName] like '%'+@FirstName+'%' 
                                                  and cl.[LastName] like '%'+@LastName+'%'
                                                  and u.[IsActive] like '%'+@IsActive+'%'";
        public const string ClientGetCountContract = @"select count([ContractId]) from [Contract] where [Client] = @ClientId";
        #endregion

        #region Contractor
        public const string ContractorGetByUserName = @"select * from [Contractor] c inner join [User] u 
                                                        on c.[User] = u.[UserId] 
                                                        where u.[Username] = @Username";
        public const string ContractorGetByUserId = @"select ctr.[ContractorId] 
                                                      from [Contractor] ctr 
                                                      inner join [User] u on u.[UserId] = ctr.[User]
                                                      where c.[UserId] = @UserId";
        public const string ContractorGetAll = "select * from [Contractor]";
        public const string ContractorGetByConsultantId = @"select ctr.[ContractorId], ctr.[User], ctr.[FirstName], ctr.[LastName], 
                                                            ctr.[Birthdate], ctr.[Gender], ctr.[Telephone1], ctr.[Telephone2],
                                                            ctr.[SortCode], ctr.[Branch], ctr.[DayInWeek]
                                                            from [Contractor] ctr , [Consultant] cs , [User] u , [Contract] c
                                                            where c.[Contractor] = ctr.[ContractorId] 
                                                            and c.[Consultant] = cs.[ConsultantId]
                                                            and u.[UserId] = ctr.[User] 
                                                            and cs.[ConsultantId] = @ConsultantId";
        public const string ContractorGetByFilter = @"select ctr.[ContractorId], ctr.[User], ctr.[FirstName], ctr.[LastName],
                                                      ctr.[Birthdate], ctr.[Gender], ctr.[Telephone1], ctr.[Telephone2], 
                                                      ctr.[SortCode], ctr.[Branch], ctr.[DayInWeek]
                                                      from [Contractor] ctr, [Consultant] cs, [User] u, [Contract] c
                                                      where c.[Contractor] = ctr.[ContractorId] 
                                                      and c.[Consultant] = cs.[ConsultantId]
                                                      and u.[UserId] = ctr.[User] 
                                                      and cs.[ConsultantId] like '%'+@ConsultantId+'%'
                                                      and u.[Username] like '%'+@Username+'%' 
                                                      and u.[Email] like '%'+@Email+'%'
                                                      and ctr.[FirstName] like '%'+@FirstName+'%' 
                                                      and ctr.[LastName] like '%'+@LastName+'%'
                                                      and u.[IsActive] like '%'+@IsActive+'%'";
        #endregion

        #region Contract
        public const string ContractGetAll = "select * from [Contract]";
        public const string ContractGetByConsultantId = "select * from [Contract] where [Consultant] = @ConsultantId";
        public const string ContractGetByContractorUserId = @"select c.[ContractId] from [Contract] c 
                                                              inner join [Contractor] c1 on c.[Contractor] = c1.[ContractorId] 
                                                              where c1.[User] = @UserId";
        public const string ContractExistContractorId = @"select top(1) [Contractor] from [Contract]
                                                          where [Contractor] = @ContractorId";
        public const string ContractGetByFilter = @"select c.[ContractId], c.[Contractor], c.[Consultant], 
                                                    c.[ContractType], c.[Client], c.[Content], 
                                                    c.[StartDate], c.[EndDate], c.[IsActive]
                                                    from [Client] cl, [Consultant] cs, [Contract] c, [Contractor] ctr, [ContractType] ct, [User] u
                                                    where cl.[ClientId] = c.[Client] 
                                                    and ctr.[ContractorId] = c.[Contractor]
                                                    and cs.[ConsultantId] = c.[Consultant] 
                                                    and ct.[ContractTypeId] = c.[ContractType] 
                                                    and u.[UserId] = ctr.[User]
                                                    and c.[ContractType] like '%'+ @ContractTypeId +'%' 
                                                    and c.[IsActive] like '%'+@IsActive+'%'
                                                    and c.[Consultant] like '%'+@ConsultantId+'%'";
        #endregion

        #region Timesheet
        public const string TimesheetGetTimesheetIdByStatusName = @"select ts.[TimesheetId] from [TimeSheet] ts 
                                                                    inner join [TimesheetStatus] tss 
                                                                    on ts.[status] = tss.[statusId]
                                                                    where tss.[statusname] = @StatusName
																	and ts.[TimesheetId] = @TimesheetId";
        public const string TimesheetGetByClientUserNameAndStatusName = @"select * from [Timesheet] ts inner join [Contract] ct
                                                                          on ts.[Contract] = ct.[ContractId] inner join [Client] cl
                                                                          on cl.[ClientId] = ct.[Client] inner join [User] u
                                                                          on cl.[User] = u.[UserId] inner join [TimesheetStatus] tss
                                                                          on tss.[StatusId] = ts.[Status]
                                                                          where u.[Username] = @Username and
                                                                          tss.[StatusName] = @StatusName 
                                                                          order by ts.[TimesheetDate] desc";
        public const string TimesheetGetByContractorId = @"select ts.[TimesheetId], ts.[Contract], ts.[Status], ts.[TimesheetDate], 
                                                           ts.[WorkingTime], ts.[Title], ts.[Description]
                                                           from [Contractor] ctr, [Consultant] cs, [User] u, [Contract] c, [Timesheet] ts
                                                           where c.[Contractor] = ctr.[ContractorId] 
                                                           and c.[Consultant] = cs.[ConsultantId]
                                                           and u.[UserId] = ctr.[User] 
                                                           and ts.[Contract] = c.[ContractId] 
                                                           and ctr.[ContractorId] = @ContractorId";
        public const string TimesheetGetByContractorUserName = @"select ts.[TimesheetId], ts.[Contract], ts.[Description], 
                                                               ts.[Title], ts.[WorkingTime], ts.[TimesheetDate], 
                                                               ts.[Status]
                                                               from [Contract] ctr inner join
                                                               [Contractor] ct on  ctr.[Contractor] = ct.[ContractorId] 
					                                           inner join [Timesheet] ts on ctr.[ContractId] = ts.[Contract] 
                                                               inner join [TimesheetStatus] tss on ts.[Status] = tss.[StatusId] 
                                                               inner join [User] u on ct.[User] =  u.[UserId]
                                                               where(u.[Username] = @Username)
                                                               order by ts.[TimesheetDate]";
        public const string TimesheetGetByFilter = @"select ts.[TimesheetId], ts.[Contract], ts.[Description], 
                                                     ts.[Title], ts.[WorkingTime], ts.[TimesheetDate], ts.[Status]
                                                     from [Contract] ctr inner join [Contractor] ct on  ctr.[Contractor] = ct.[ContractorId] 
					                                 inner join [Timesheet] ts on ctr.[ContractId] = ts.[Contract] 
                                                     inner join [TimesheetStatus] tss on ts.[Status] = tss.[StatusId] 
                                                     inner join [User] u on ct.[User] = u.[UserId]
                                                     where (tss.[StatusId] = @StatusId) 
													 and (u.[Username] = @Username) order by ts.[TimesheetDate]";
        public const string TimesheetGetByFilterRange = @"select ts.[TimesheetId], ts.[Contract], ts.[Description], 
                                                          ts.[Title], ts.[WorkingTime], ts.[TimesheetDate], ts.[Status]
                                                          from [Contract] ctr inner join [Contractor] ct on  ctr.[Contractor] = ct.[ContractorId] 
					                                      inner join [Timesheet] ts on ctr.[ContractId] = ts.[Contract] 
                                                          inner join [TimesheetStatus] tss on ts.[Status] = tss.[StatusId] 
                                                          inner join [User] u on ct.[User] = u.[UserId]
                                                          where (tss.[StatusId] = @StatusId) 
														  and (u.[Username]=@Username) and (ts.[TimesheetDate] between @FromDate and @ToDate) order by ts.[TimesheetDate]";
        #endregion

        #region TimesheetStatus
        public const string TimesheetStatusGetAll = "select * from [TimesheetStatus]";
        public const string TimesheetStatusGetStatusIdByStatusName = @"select [StatusId] from [TimesheetStatus] 
                                                                       where [StatusName] = @StatusName";
        public const string TimesheetStatusGetByStatusName = @"select * from [TimesheetStatus] 
                                                               where [StatusName] = @StatusName";
        #endregion

        #region Reason
        public const string ReasonGetReasonTextByStatusName = @"select rs.[ReasonText] from [Reason] rs 
                                                                inner join [Timesheet] ts on rs.[Timesheet] = ts.[TimesheetId] 
                                                                inner join [TimesheetStatus] tss on ts.[Status] = tss.[StatusId]
                                                                where tss.[Statusname] = @StatusName 
                                                                and ts.[TimesheetId] = @TimesheetId";
        #endregion

        #region Role
        public const string RoleGetAll = "select * from [Role]";
        public const string RoleGetRoleIdByRoleName = @"select [RoleId] from [Role] 
                                                        where [RoleName] = @RoleName";
        #endregion

        #region Message
        public const string MessageGetByToUserIdAndIsReceive = @"select * from [Message] 
                                                                 where [IsReceive] = @IsReceive
                                                                 and [To] = @UserId";
        public const string MessageCountByToUserIdAndIsReceive = @"select count(*) from [Message] 
                                                                   where [IsReceive] = @IsReceive
                                                                   and [To] = @UserId";
        public const string MessageGetTopByToUserId = @"select top(@Top)* from [Message] 
                                                        where [To] = @UserId
                                                        order by [SendDate] desc";
        public const string MessageGetAllByToUserId = @"select * from [Message] 
                                                        where [To] = @UserId
                                                        order by [SendDate] desc";
        #endregion

        #region Paramater
        public const string ParameterIsReceive = "@IsReceive";
        public const string ParameterTop = "@Top";
        public const string ParameterContractId = "@ContractId";
        public const string ParameterClientId = "@ClientId";
        public const string ParameterConsultantId = "@ConsultantId";
        public const string ParameterContractorId = "@ContractorId";
        public const string ParameterUserId = "@UserId";
        public const string ParameterRoleName = "@RoleName";
        public const string ParameterUserName = "@Username";
        public const string ParameterPassword = "@Password";
        public const string ParameterRoleId = "@RoleId";
        public const string ParameterContractTypeName = "@ContractTypeName";
        public const string ParameterContractTypeId = "@ContractTypeId";
        public const string ParameterStatusId = "@StatusId";
        public const string ParameterTimesheetId = "@TimesheetId";
        public const string ParameterStatusName = "@StatusName";
        public const string ParameterFromDate = "@FromDate";
        public const string ParameterToDate = "@ToDate";
        public const string ParameterTimesheetDate = "@TimesheetDate";
        public const string ParameterFirstName = @"FirstName";
        public const string ParameterLastName = "@LastName";
        public const string ParameterIsActive = "@IsActive";
        public const string ParameterEmail = "@Email";
        #endregion
    }
}
